{
 "cells": [
  {
   "cell_type": "code",
   "id": "initial_id",
   "metadata": {
    "collapsed": true,
    "ExecuteTime": {
     "end_time": "2025-11-01T14:22:40.158910Z",
     "start_time": "2025-11-01T14:22:33.887872Z"
    }
   },
   "source": [
    "import time\n",
    "\n",
    "import pandas as pd\n",
    "from data_resource.data_bases import engine, MinuteBars, IndexConstituent, IndexDaily, StockInfo\n",
    "from utilities.utilities_func import init_ts, write_to_db, bulk_insert_with_orm, get_session\n",
    "from pathlib import Path\n",
    "from tqdm import tqdm\n",
    "from sqlalchemy import case\n",
    "from data_resource.data_process import ts_bar_daily\n",
    "from utilities.utilities_func import clear_financials\n",
    "pro = init_ts()"
   ],
   "outputs": [],
   "execution_count": 1
  },
  {
   "cell_type": "code",
   "id": "10fb29e841a05bec",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-10-12T02:51:53.943710Z",
     "start_time": "2025-10-12T02:51:53.471509Z"
    }
   },
   "source": [
    "# pro.index_basic(name='')\n",
    "x = pro.index_basic()\n",
    "x[x['name'].str.contains('小盘价值')]"
   ],
   "outputs": [
    {
     "data": {
      "text/plain": [
       "           ts_code                 name market publisher category base_date  \\\n",
       "2680     143869.MI  MSCI东欧新兴市场(除俄)中小盘价值   MSCI    MSCI指数     价值指数  20070531   \n",
       "2704     146610.MI        MSCI巴基斯坦中小盘价值   MSCI    MSCI指数     价值指数      None   \n",
       "2710     149111.MI         MSCI阿根廷中小盘价值   MSCI    MSCI指数     价值指数      None   \n",
       "2920     399377.SZ                 小盘价值   SZSE      巨潮公司     风格指数  20021231   \n",
       "4163     661735.MI        MSCI澳大利亚中小盘价值   MSCI    MSCI指数     价值指数  20070531   \n",
       "...            ...                  ...    ...       ...      ...       ...   \n",
       "4259     661836.MI          MSCI泰国中小盘价值   MSCI    MSCI指数     价值指数  20070531   \n",
       "4260     661837.MI         MSCI土耳其中小盘价值   MSCI    MSCI指数     价值指数  20070531   \n",
       "4261     661839.MI          MSCI中华中小盘价值   MSCI    MSCI指数     价值指数  20070531   \n",
       "5264    818300V.CI     标普中国A股小盘价值指数(退市)    OTH    标准普尔公司       其他  20040227   \n",
       "5265  818300VTR.CI  标普中国A股小盘价值总收益指数(退市)    OTH    标准普尔公司       其他  20040227   \n",
       "\n",
       "      base_point list_date  \n",
       "2680      1000.0  20090317  \n",
       "2704         NaN  20170601  \n",
       "2710         NaN  20190529  \n",
       "2920      1000.0  20100104  \n",
       "4163      1000.0  20070605  \n",
       "...          ...       ...  \n",
       "4259      1000.0  20070605  \n",
       "4260      1000.0  20070605  \n",
       "4261      1000.0  20070605  \n",
       "5264      1000.0  20070730  \n",
       "5265      1000.0  20070730  \n",
       "\n",
       "[105 rows x 8 columns]"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ts_code</th>\n",
       "      <th>name</th>\n",
       "      <th>market</th>\n",
       "      <th>publisher</th>\n",
       "      <th>category</th>\n",
       "      <th>base_date</th>\n",
       "      <th>base_point</th>\n",
       "      <th>list_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2680</th>\n",
       "      <td>143869.MI</td>\n",
       "      <td>MSCI东欧新兴市场(除俄)中小盘价值</td>\n",
       "      <td>MSCI</td>\n",
       "      <td>MSCI指数</td>\n",
       "      <td>价值指数</td>\n",
       "      <td>20070531</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>20090317</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2704</th>\n",
       "      <td>146610.MI</td>\n",
       "      <td>MSCI巴基斯坦中小盘价值</td>\n",
       "      <td>MSCI</td>\n",
       "      <td>MSCI指数</td>\n",
       "      <td>价值指数</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20170601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2710</th>\n",
       "      <td>149111.MI</td>\n",
       "      <td>MSCI阿根廷中小盘价值</td>\n",
       "      <td>MSCI</td>\n",
       "      <td>MSCI指数</td>\n",
       "      <td>价值指数</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20190529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2920</th>\n",
       "      <td>399377.SZ</td>\n",
       "      <td>小盘价值</td>\n",
       "      <td>SZSE</td>\n",
       "      <td>巨潮公司</td>\n",
       "      <td>风格指数</td>\n",
       "      <td>20021231</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>20100104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4163</th>\n",
       "      <td>661735.MI</td>\n",
       "      <td>MSCI澳大利亚中小盘价值</td>\n",
       "      <td>MSCI</td>\n",
       "      <td>MSCI指数</td>\n",
       "      <td>价值指数</td>\n",
       "      <td>20070531</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>20070605</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4259</th>\n",
       "      <td>661836.MI</td>\n",
       "      <td>MSCI泰国中小盘价值</td>\n",
       "      <td>MSCI</td>\n",
       "      <td>MSCI指数</td>\n",
       "      <td>价值指数</td>\n",
       "      <td>20070531</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>20070605</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4260</th>\n",
       "      <td>661837.MI</td>\n",
       "      <td>MSCI土耳其中小盘价值</td>\n",
       "      <td>MSCI</td>\n",
       "      <td>MSCI指数</td>\n",
       "      <td>价值指数</td>\n",
       "      <td>20070531</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>20070605</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4261</th>\n",
       "      <td>661839.MI</td>\n",
       "      <td>MSCI中华中小盘价值</td>\n",
       "      <td>MSCI</td>\n",
       "      <td>MSCI指数</td>\n",
       "      <td>价值指数</td>\n",
       "      <td>20070531</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>20070605</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5264</th>\n",
       "      <td>818300V.CI</td>\n",
       "      <td>标普中国A股小盘价值指数(退市)</td>\n",
       "      <td>OTH</td>\n",
       "      <td>标准普尔公司</td>\n",
       "      <td>其他</td>\n",
       "      <td>20040227</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>20070730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5265</th>\n",
       "      <td>818300VTR.CI</td>\n",
       "      <td>标普中国A股小盘价值总收益指数(退市)</td>\n",
       "      <td>OTH</td>\n",
       "      <td>标准普尔公司</td>\n",
       "      <td>其他</td>\n",
       "      <td>20040227</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>20070730</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>105 rows × 8 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 27
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "dd0077ecbe304201",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-08-30T05:48:23.713304Z",
     "start_time": "2025-08-30T05:48:21.418986Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "********* 数据总量：6000, 小于10000, 采用单线程写入 **********\n",
      "\n",
      " 表index_constituent:   数据导入完成：共 6000 行，失败 0 行\n"
     ]
    }
   ],
   "source": [
    "\"\"\"\n",
    "    中证1000\n",
    "\"\"\"\n",
    "x = pro.index_weight(index_code='000852.SH')\n",
    "x['con_code'] = x['con_code'].apply(lambda p: p[0:6])\n",
    "x['trade_date'] = x['trade_date'].apply(lambda r: pd.to_datetime(r).date())\n",
    "\n",
    "# from utilities.utilities_func import bulk_insert_with_orm\n",
    "# bulk_insert_with_orm(x, 'index_constituent', engine)\n",
    "write_to_db('index_constituent', engine, IndexConstituent, x)\n",
    "\n",
    "\"\"\"\n",
    "    中证800\n",
    "\"\"\"\n",
    "x = pro.index_weight(index_code='000906.SH')\n",
    "x['con_code'] = x['con_code'].apply(lambda p: p[0:6])\n",
    "x['trade_date'] = x['trade_date'].apply(lambda r: pd.to_datetime(r).date())\n",
    "\n",
    "write_to_db('index_constituent', engine, IndexConstituent, x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "3bda9db808cd9efb",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-08-31T05:48:02.183109Z",
     "start_time": "2025-08-31T05:48:01.505796Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "********* 数据总量：6000, 小于10000, 采用单线程写入 **********\n",
      "\n",
      " 表index_constituent:   数据导入完成：共 6000 行，失败 0 行\n"
     ]
    }
   ],
   "source": [
    "# 中证红利指数成分股导入\n",
    "x = pro.index_weight(index_code='000922.CSI')\n",
    "x['con_code'] = x['con_code'].apply(lambda p: p[0:6])\n",
    "x['trade_date'] = pd.to_datetime(x['trade_date']).dt.date\n",
    "\n",
    "write_to_db('index_constituent', engine, IndexConstituent, x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "703ecb6ec73ccee0",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "    分钟行情数据写入\n",
    "\"\"\"\n",
    "path = r\"D:\\高频数据\\00～25\\data\"\n",
    "x = [str(file) for file in Path(path).rglob('*.csv')]\n",
    "errorlist = []\n",
    "\n",
    "for i in tqdm(x):\n",
    "    _data = pd.read_csv(i, usecols=lambda col: col != \"Unnamed: 0\")\n",
    "    # 判断版式\n",
    "    if _data.shape[1] == 9:\n",
    "        _data1 = _data.copy()\n",
    "        # 标准化股票代码\n",
    "        _data1['code'] = _data1['code'].apply(lambda l: l[3:])\n",
    "        # 合并时间字段\n",
    "        _data1[\"trading\"] = pd.to_datetime(_data1[\"date\"].str.cat(_data1[\"time\"], sep=\" \"), format=\"%Y/%m/%d %H:%M\")\n",
    "        _data1.drop(columns=[\"date\", \"time\"], inplace=True)\n",
    "        _data1.rename(columns={'mount': 'amount'}, inplace=True)\n",
    "    elif _data.shape[1] == 7:\n",
    "        _code = i.split('.')[-2]\n",
    "        _data1 = _data.copy()\n",
    "        _data1['code'] = _code\n",
    "        _data1['datetime'] = pd.to_datetime(_data1['datetime'], format=\"%Y/%m/%d %H:%M\")\n",
    "        _data1.rename(columns={'datetime': 'trading'}, inplace=True)\n",
    "    else:\n",
    "        print(f\"------------------- 版式变化，路径：{i}\")\n",
    "        errorlist.append(i)\n",
    "        continue\n",
    "    \n",
    "    if 'volume' in _data1.columns:\n",
    "        _data1.rename(columns={'volume': 'volumn'}, inplace=True)\n",
    "        \n",
    "    write_to_db('minute_bars', engine, MinuteBars, _data1)"
   ]
  },
  {
   "cell_type": "code",
   "id": "63047561f3e6188a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-10-12T13:14:26.770223Z",
     "start_time": "2025-10-12T13:12:11.301320Z"
    }
   },
   "source": [
    "# 即期国债收益率\n",
    "\n",
    "bond_term_cn = []\n",
    "print('-------------- 开始获取中国国债收益率 -------------')\n",
    "count = 0\n",
    "for i in tqdm(range(2010, 2026)):\n",
    "    _start = f\"{i}0101\"\n",
    "    _end = f\"{i}1231\"\n",
    "    _bond1 = pro.yc_cb(start_date=_start, end_date=_end, curve_term=10, curve_type=\"1\")\n",
    "    _bond2 = pro.yc_cb(start_date=_start, end_date=_end, curve_term=5, curve_type=\"1\")\n",
    "    _bond3 = pro.yc_cb(start_date=_start, end_date=_end, curve_term=1, curve_type=\"1\")\n",
    "    bond_term_cn.append(_bond1)\n",
    "    bond_term_cn.append(_bond2)\n",
    "    bond_term_cn.append(_bond3)\n",
    "    count += 3\n",
    "    if (count % 18 == 0) and (count != 0):\n",
    "        time.sleep(60)\n",
    "        \n",
    "bond_term_cn = pd.concat(bond_term_cn)\n",
    "bond_term_cn.reset_index(drop=True, inplace=True)\n",
    "bulk_insert_with_orm(bond_term_cn, 'bond_term_cn', engine)\n",
    "\n",
    "# 美债收益率\n",
    "bond_term_us = []\n",
    "count = 0\n",
    "print('-------------- 开始获取美国国债收益率 -------------')\n",
    "for i in range(2010, 2026):\n",
    "    _start = f\"{i}0101\"\n",
    "    _end = f\"{i}1231\"\n",
    "    _bond1 = pro.us_tycr(start_date=_start, end_date=_end)\n",
    "    bond_term_us.append(_bond1)\n",
    "    count += 1\n",
    "    if (count % 20 == 0) and (count != 0):\n",
    "        time.sleep(60)\n",
    "\n",
    "bond_term_us = pd.concat(bond_term_us)\n",
    "bond_term_us.reset_index(drop=True, inplace=True)\n",
    "bulk_insert_with_orm(bond_term_us, 'bond_term_us', engine)"
   ],
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-------------- 开始获取中国国债收益率 -------------\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 16/16 [02:11<00:00,  8.19s/it]\n",
      "C:\\Users\\caitao\\AppData\\Local\\Temp\\ipykernel_36536\\4281369568.py:19: FutureWarning: The behavior of DataFrame concatenation with empty or all-NA entries is deprecated. In a future version, this will no longer exclude empty or all-NA columns when determining the result dtypes. To retain the old behavior, exclude the relevant entries before the concat operation.\n",
      "  bond_term_cn = pd.concat(bond_term_cn)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "表bond_term_cn已创建\n",
      "********* 数据总量：7002, 小于10000, 采用单线程写入 **********\n",
      "\n",
      " 表bond_term_cn:   数据导入完成：共 7002 行，失败 0 行\n",
      "-------------- 开始获取美国国债收益率 -------------\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\caitao\\AppData\\Local\\Temp\\ipykernel_36536\\4281369568.py:36: FutureWarning: The behavior of DataFrame concatenation with empty or all-NA entries is deprecated. In a future version, this will no longer exclude empty or all-NA columns when determining the result dtypes. To retain the old behavior, exclude the relevant entries before the concat operation.\n",
      "  bond_term_us = pd.concat(bond_term_us)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "********* 数据总量：3946, 小于10000, 采用单线程写入 **********\n",
      "\n",
      " 表bond_term_us:   数据导入完成：共 3946 行，失败 0 行\n"
     ]
    }
   ],
   "execution_count": 9
  },
  {
   "cell_type": "code",
   "id": "d3b4151118ddfe77",
   "metadata": {},
   "source": [
    "# 获取指数行情\n",
    "\"\"\"\n",
    "中证红利：000922.CSI\n",
    "沪深300：000300.SH\n",
    "中证800：000906.SH\n",
    "中证1000：000852.SH\n",
    "中证全指：000985.CSI\n",
    "全指价值：000058.SH\n",
    "全指成长：000057.SH\n",
    "大盘价值：399373.SZ\n",
    "小盘价值：399377.SZ\n",
    "\"\"\"\n",
    "\n",
    "count = 0\n",
    "index_daily = []\n",
    "index_name = {\n",
    "    '000922.CSI': '中证红利',\n",
    "    '000300.SH': '沪深300',\n",
    "    '000906.SH': '中证800',\n",
    "    '000852.SH': '中证1000',\n",
    "    '000985.CSI': '中证全指',\n",
    "    '000058.SH': '全指价值',\n",
    "    '000057.SH': '全指成长',\n",
    "    '399373.SZ': '大盘价值',\n",
    "    '399377.SZ': '小盘价值'\n",
    "}\n",
    "for j in index_name.keys():\n",
    "    for i in tqdm(range(2010, 2026)):\n",
    "        _start = f\"{i}0101\"\n",
    "        _end = f\"{i}1231\"\n",
    "        _r = pro.index_daily(ts_code=j, start_date=_start, end_date=_end)\n",
    "        _r['index_name'] = index_name[j]\n",
    "        index_daily.append(_r)\n",
    "        count += 1\n",
    "        if (count % 20 == 0) and (count != 0):\n",
    "            time.sleep(60)\n",
    "    print(f'{index_name[j]} 获取完毕')\n",
    "            \n",
    "index_daily = pd.concat(index_daily)\n",
    "index_daily.reset_index(drop=True, inplace=True)\n",
    "index_daily['trade_date'] = pd.to_datetime(index_daily['trade_date']).dt.date\n",
    "\n",
    "bulk_insert_with_orm(index_daily, 'index_daily', engine)\n",
    "# write_to_db('index_daily', engine, IndexDaily, index_daily)"
   ],
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "code",
   "id": "63278cc95fe2ecc6",
   "metadata": {},
   "source": [
    "# 个股分红数据\n",
    "with get_session(engine) as session:\n",
    "    stocks = session.query(\n",
    "        case(\n",
    "            (StockInfo.ticker.startswith('6'), StockInfo.ticker + '.SH'),\n",
    "            (StockInfo.ticker.startswith('0'), StockInfo.ticker + '.SZ'),\n",
    "            (StockInfo.ticker.startswith('3'), StockInfo.ticker + '.SZ'),\n",
    "            (StockInfo.ticker.startswith('8'), StockInfo.ticker + '.BJ'),\n",
    "            (StockInfo.ticker.startswith('4'), StockInfo.ticker + '.BJ'),\n",
    "            (StockInfo.ticker.startswith('9'), StockInfo.ticker + '.BJ'),\n",
    "            else_=StockInfo.ticker\n",
    "        ).label('code')\n",
    "    ).where(StockInfo.status == 'L').all()\n",
    "\n",
    "results = []\n",
    "count = 0\n",
    "print('-------------- 获取股股分红数据 -------------')\n",
    "for code in tqdm(stocks):\n",
    "    _code = code[0]\n",
    "    _r = pro.dividend(ts_code=_code)\n",
    "    if _r.empty:\n",
    "        print(f'{_code}分红数据为空')\n",
    "        continue\n",
    "    _r['end_date'] = pd.to_datetime(_r['end_date']).dt.date\n",
    "    _r['ann_date'] = pd.to_datetime(_r['ann_date']).dt.date\n",
    "    _r['record_date'] = pd.to_datetime(_r['record_date']).dt.date\n",
    "    _r['ex_date'] = pd.to_datetime(_r['ex_date']).dt.date\n",
    "    _r['pay_date']  = pd.to_datetime(_r['pay_date']).dt.date\n",
    "    _r['div_listdate'] = pd.to_datetime(_r['div_listdate']).dt.date\n",
    "    _r['imp_ann_date'] = pd.to_datetime(_r['imp_ann_date']).dt.date\n",
    "    \n",
    "    results.append(_r)\n",
    "    count += 1\n",
    "    if (count % 500 == 0) and (count != 0):\n",
    "        time.sleep(60)\n",
    "\n",
    "results = pd.concat(results)\n",
    "print(results.shape)"
   ],
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "code",
   "id": "7f7ef437d36f4f44",
   "metadata": {},
   "source": [
    "\"\"\"\n",
    "获取前复权行情\n",
    "\"\"\"\n",
    "# 获取股票列表\n",
    "_sql1 = \"\"\"\n",
    "    select ticker, list_date from quant_research.basic_info_stock\n",
    "    where status='L' and market in ('主板', '创业板', '科创板')\n",
    "\"\"\"\n",
    "_pool = pd.read_sql(_sql1, engine)\n",
    "result = []\n",
    "\n",
    "_start = '20100101'\n",
    "_end = '20250905'\n",
    "for num in tqdm(range(len(_pool))):\n",
    "    _stock = _pool.iloc[num, 0]\n",
    "    if _stock[0] == '6':\n",
    "        _code = _stock + '.SH'\n",
    "    else:\n",
    "        _code = _stock + '.SZ'\n",
    "    # _start = _pool.iloc[num, 1].strftime('%Y%m%d')\n",
    "\n",
    "    # 获取行情数据\n",
    "    _data = ts_bar_daily(_code, _start, _end, adj='qfq')\n",
    "    if _data is None:\n",
    "        print(f\"{_code}无法提取行情数据，时间区间{_start}-{_end}\")\n",
    "    elif _data.empty:\n",
    "        print(f\"{_code}无法提取行情数据，时间区间{_start}-{_end}\")\n",
    "    else:\n",
    "        result.append(_data)\n",
    "\n",
    "    if (num % 450 == 0) & (num > 0):\n",
    "        time.sleep(60)\n",
    "\n",
    "result = pd.concat(result)\n",
    "result.reset_index(inplace=True, drop=True)\n",
    "result.rename(columns={'ts_code': 'code'}, inplace=True)\n",
    "result['code'] = result['code'].apply(lambda n: n[0:6])\n",
    "result['trade_date'] = pd.to_datetime(result['trade_date']).dt.date\n",
    "\n",
    "print(\"-------------开始写入个股日线行情数据库，总共有%d条数据-------------\" % len(result))\n",
    "bulk_insert_with_orm(result, 'market_daily_qfq', engine)\n",
    "print(\"-------------个股日线行情数据写入完成---------------------\")"
   ],
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "17a58edf647bb54e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-16T05:12:56.398227Z",
     "start_time": "2025-09-16T05:12:55.457967Z"
    }
   },
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "ETF基金简称\n",
    "\"\"\"\n",
    "a = pro.etf_basic(list_status='L')\n",
    "a.to_excel('etf_basic.xlsx', index=False)"
   ]
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-10-12T13:09:36.700564Z",
     "start_time": "2025-10-12T13:09:35.715474Z"
    }
   },
   "cell_type": "code",
   "source": [
    "\"\"\"\n",
    "宏观数据\n",
    "\"\"\"\n",
    "\n",
    "# 社融月度\n",
    "sf = pro.sf_month(start_m='200001', end_m='202510')\n",
    "bulk_insert_with_orm(sf, 'macro_social_financing_cn', engine)\n",
    "\n",
    "# CPI数据\n",
    "cpi = pro.cn_cpi(start_m='200001', end_m='202510')\n",
    "bulk_insert_with_orm(cpi, 'macro_cpi_cn', engine)\n",
    "\n",
    "# PPI数据\n",
    "ppi = pro.cn_ppi(start_m='200001', end_m='202510')\n",
    "bulk_insert_with_orm(cpi, 'macro_ppi_cn', engine)"
   ],
   "id": "caf07e690697d833",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "表macro_social_financing_cn已创建\n",
      "********* 数据总量：283, 小于10000, 采用单线程写入 **********\n",
      "\n",
      " 表macro_social_financing_cn:   数据导入完成：共 283 行，失败 0 行\n",
      "表macro_cpi_cn已创建\n",
      "********* 数据总量：308, 小于10000, 采用单线程写入 **********\n",
      "\n",
      " 表macro_cpi_cn:   数据导入完成：共 308 行，失败 0 行\n",
      "表macro_ppi_cn已创建\n",
      "********* 数据总量：308, 小于10000, 采用单线程写入 **********\n",
      "\n",
      " 表macro_ppi_cn:   数据导入完成：共 308 行，失败 0 行\n"
     ]
    }
   ],
   "execution_count": 8
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-10-14T05:23:30.014650Z",
     "start_time": "2025-10-14T05:23:29.559128Z"
    }
   },
   "cell_type": "code",
   "source": "pro.yc_cb(start_date=\"20250901\", end_date=\"20251015\", curve_term=10, curve_type=\"1\")",
   "id": "376d909e99b5e997",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   trade_date  ts_code curve_name curve_type  curve_term   yield\n",
       "0    20251013  1001.CB  中债国债收益率曲线          1        10.0  1.8602\n",
       "1    20251011  1001.CB  中债国债收益率曲线          1        10.0  1.8421\n",
       "2    20251010  1001.CB  中债国债收益率曲线          1        10.0  1.8681\n",
       "3    20251009  1001.CB  中债国债收益率曲线          1        10.0  1.8690\n",
       "4    20250930  1001.CB  中债国债收益率曲线          1        10.0  1.8836\n",
       "5    20250929  1001.CB  中债国债收益率曲线          1        10.0  1.9102\n",
       "6    20250928  1001.CB  中债国债收益率曲线          1        10.0  1.9011\n",
       "7    20250926  1001.CB  中债国债收益率曲线          1        10.0  1.8996\n",
       "8    20250925  1001.CB  中债国债收益率曲线          1        10.0  1.9065\n",
       "9    20250924  1001.CB  中债国债收益率曲线          1        10.0  1.9263\n",
       "10   20250923  1001.CB  中债国债收益率曲线          1        10.0  1.9003\n",
       "11   20250922  1001.CB  中债国债收益率曲线          1        10.0  1.8880\n",
       "12   20250919  1001.CB  中债国债收益率曲线          1        10.0  1.9026\n",
       "13   20250918  1001.CB  中债国债收益率曲线          1        10.0  1.8780\n",
       "14   20250917  1001.CB  中债国债收益率曲线          1        10.0  1.8586\n",
       "15   20250916  1001.CB  中债国债收益率曲线          1        10.0  1.8780\n",
       "16   20250915  1001.CB  中债国债收益率曲线          1        10.0  1.8978\n",
       "17   20250912  1001.CB  中债国债收益率曲线          1        10.0  1.8920\n",
       "18   20250911  1001.CB  中债国债收益率曲线          1        10.0  1.8994\n",
       "19   20250910  1001.CB  中债国债收益率曲线          1        10.0  1.9251\n",
       "20   20250909  1001.CB  中债国债收益率曲线          1        10.0  1.8884\n",
       "21   20250908  1001.CB  中债国债收益率曲线          1        10.0  1.8750\n",
       "22   20250905  1001.CB  中债国债收益率曲线          1        10.0  1.8484\n",
       "23   20250904  1001.CB  中债国债收益率曲线          1        10.0  1.8280\n",
       "24   20250903  1001.CB  中债国债收益率曲线          1        10.0  1.8200\n",
       "25   20250902  1001.CB  中债国债收益率曲线          1        10.0  1.8387\n",
       "26   20250901  1001.CB  中债国债收益率曲线          1        10.0  1.8479"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>trade_date</th>\n",
       "      <th>ts_code</th>\n",
       "      <th>curve_name</th>\n",
       "      <th>curve_type</th>\n",
       "      <th>curve_term</th>\n",
       "      <th>yield</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20251013</td>\n",
       "      <td>1001.CB</td>\n",
       "      <td>中债国债收益率曲线</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.8602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20251011</td>\n",
       "      <td>1001.CB</td>\n",
       "      <td>中债国债收益率曲线</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.8421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20251010</td>\n",
       "      <td>1001.CB</td>\n",
       "      <td>中债国债收益率曲线</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.8681</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20251009</td>\n",
       "      <td>1001.CB</td>\n",
       "      <td>中债国债收益率曲线</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.8690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20250930</td>\n",
       "      <td>1001.CB</td>\n",
       "      <td>中债国债收益率曲线</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.8836</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>20250929</td>\n",
       "      <td>1001.CB</td>\n",
       "      <td>中债国债收益率曲线</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.9102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>20250928</td>\n",
       "      <td>1001.CB</td>\n",
       "      <td>中债国债收益率曲线</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.9011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>20250926</td>\n",
       "      <td>1001.CB</td>\n",
       "      <td>中债国债收益率曲线</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.8996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>20250925</td>\n",
       "      <td>1001.CB</td>\n",
       "      <td>中债国债收益率曲线</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.9065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>20250924</td>\n",
       "      <td>1001.CB</td>\n",
       "      <td>中债国债收益率曲线</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.9263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>20250923</td>\n",
       "      <td>1001.CB</td>\n",
       "      <td>中债国债收益率曲线</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.9003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>20250922</td>\n",
       "      <td>1001.CB</td>\n",
       "      <td>中债国债收益率曲线</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.8880</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>20250919</td>\n",
       "      <td>1001.CB</td>\n",
       "      <td>中债国债收益率曲线</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.9026</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>20250918</td>\n",
       "      <td>1001.CB</td>\n",
       "      <td>中债国债收益率曲线</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.8780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>20250917</td>\n",
       "      <td>1001.CB</td>\n",
       "      <td>中债国债收益率曲线</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.8586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>20250916</td>\n",
       "      <td>1001.CB</td>\n",
       "      <td>中债国债收益率曲线</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.8780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>20250915</td>\n",
       "      <td>1001.CB</td>\n",
       "      <td>中债国债收益率曲线</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.8978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>20250912</td>\n",
       "      <td>1001.CB</td>\n",
       "      <td>中债国债收益率曲线</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.8920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>20250911</td>\n",
       "      <td>1001.CB</td>\n",
       "      <td>中债国债收益率曲线</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.8994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>20250910</td>\n",
       "      <td>1001.CB</td>\n",
       "      <td>中债国债收益率曲线</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.9251</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>20250909</td>\n",
       "      <td>1001.CB</td>\n",
       "      <td>中债国债收益率曲线</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.8884</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>20250908</td>\n",
       "      <td>1001.CB</td>\n",
       "      <td>中债国债收益率曲线</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.8750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>20250905</td>\n",
       "      <td>1001.CB</td>\n",
       "      <td>中债国债收益率曲线</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.8484</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>20250904</td>\n",
       "      <td>1001.CB</td>\n",
       "      <td>中债国债收益率曲线</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.8280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>20250903</td>\n",
       "      <td>1001.CB</td>\n",
       "      <td>中债国债收益率曲线</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.8200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>20250902</td>\n",
       "      <td>1001.CB</td>\n",
       "      <td>中债国债收益率曲线</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.8387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>20250901</td>\n",
       "      <td>1001.CB</td>\n",
       "      <td>中债国债收益率曲线</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.8479</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 4
  },
  {
   "metadata": {},
   "cell_type": "code",
   "outputs": [],
   "execution_count": null,
   "source": "",
   "id": "aa8a774eaada5527"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-11-01T14:24:13.331830Z",
     "start_time": "2025-11-01T14:23:03.933526Z"
    }
   },
   "cell_type": "code",
   "source": [
    "\"\"\"财务数据导入查缺补漏\"\"\"\n",
    "date = '20250930'\n",
    "_cashflow1 = pro.cashflow_vip(period=date, report_type=1)\n",
    "_cashflow2 = pro.cashflow_vip(period=date, report_type=2)\n",
    "_cashflow5 = pro.cashflow_vip(period=date, report_type=5)\n",
    "\n",
    "_income1 = pro.income_vip(period=date, report_type=1)\n",
    "_income2 = pro.income_vip(period=date, report_type=2)\n",
    "_income5 = pro.income_vip(period=date, report_type=5)\n",
    "\n",
    "_bs1 = pro.balancesheet_vip(period=date, report_type=1)\n",
    "_bs5 = pro.balancesheet_vip(period=date, report_type=5)\n",
    "\n",
    "if not _cashflow1.empty:\n",
    "    _cf1 = clear_financials(_cashflow1)\n",
    "    bulk_insert_with_orm(_cf1, \"financials_cashflow_lt\", engine,\n",
    "                        unique_columns=['ticker', 'f_ann_date', 'end_date', 'update_flag'])\n",
    "    if not _cashflow2.empty:\n",
    "                _cf2 = clear_financials(_cashflow2)\n",
    "                bulk_insert_with_orm(_cf2, \"financials_cashflow_quarter\", engine,\n",
    "                                     unique_columns=['ticker', 'f_ann_date', 'end_date', 'update_flag'])\n",
    "    if not _cashflow5.empty:\n",
    "                _cf5 = clear_financials(_cashflow5)\n",
    "                bulk_insert_with_orm(_cf5, \"financials_cashflow_beforeUpdate\", engine,\n",
    "                                     unique_columns=['ticker', 'f_ann_date', 'end_date', 'update_flag'])\n",
    "\n",
    "    if not _income1.empty:\n",
    "                _is1 = clear_financials(_income1)\n",
    "                bulk_insert_with_orm(_is1, \"financials_incomeState_lt\", engine,\n",
    "                                     unique_columns=['ticker', 'f_ann_date', 'end_date', 'update_flag'])\n",
    "    if not _income2.empty:\n",
    "                _is2 = clear_financials(_income2)\n",
    "                bulk_insert_with_orm(_is2, \"financials_incomeState_quarter\", engine,\n",
    "                                     unique_columns=['ticker', 'f_ann_date', 'end_date', 'update_flag'])\n",
    "    if not _income5.empty:\n",
    "                _is5 = clear_financials(_income5)\n",
    "                bulk_insert_with_orm(_is5, \"financials_incomeState_beforeUpdate\", engine,\n",
    "                                     unique_columns=['ticker', 'f_ann_date', 'end_date', 'update_flag'])\n",
    "\n",
    "    if not _bs1.empty:\n",
    "                _bs1 = clear_financials(_bs1)\n",
    "                bulk_insert_with_orm(_bs1, \"financials_bs_lt\", engine,\n",
    "                                     unique_columns=['ticker', 'f_ann_date', 'end_date', 'update_flag'])\n",
    "    if not _bs5.empty:\n",
    "                _bs5 = clear_financials(_bs5)\n",
    "                bulk_insert_with_orm(_bs5, \"financials_bs_beforeUpdate\", engine,\n",
    "                                     unique_columns=['ticker', 'f_ann_date', 'end_date', 'update_flag'])"
   ],
   "id": "93800173914e8af3",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "********* 数据总量：6400, 小于10000, 采用单线程写入 **********\n",
      "\n",
      " 表financials_cashflow_lt:   数据导入完成：共 6400 行，失败 6400 行\n",
      "********* 数据总量：5458, 小于10000, 采用单线程写入 **********\n",
      "\n",
      " 表financials_cashflow_quarter:   数据导入完成：共 5458 行，失败 5458 行\n",
      "********* 数据总量：2, 小于10000, 采用单线程写入 **********\n",
      "\n",
      " 表financials_cashflow_beforeUpdate:   数据导入完成：共 2 行，失败 2 行\n",
      "********* 数据总量：6381, 小于10000, 采用单线程写入 **********\n",
      "\n",
      " 表financials_incomeState_lt:   数据导入完成：共 6381 行，失败 6381 行\n",
      "********* 数据总量：5448, 小于10000, 采用单线程写入 **********\n",
      "\n",
      " 表financials_incomeState_quarter:   数据导入完成：共 5448 行，失败 5448 行\n",
      "********* 数据总量：5759, 小于10000, 采用单线程写入 **********\n",
      "\n",
      " 表financials_bs_lt:   数据导入完成：共 5759 行，失败 5759 行\n",
      "********* 数据总量：1, 小于10000, 采用单线程写入 **********\n",
      "\n",
      " 表financials_bs_beforeUpdate:   数据导入完成：共 1 行，失败 1 行\n"
     ]
    }
   ],
   "execution_count": 2
  },
  {
   "metadata": {},
   "cell_type": "code",
   "outputs": [],
   "execution_count": null,
   "source": "",
   "id": "d29168f6261f09d4"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "quantCN",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
